CREATE OR REPLACE EDITIONABLE PACKAGE BODY "SCOTT"."PKG_TOOL" is

  ---------------------------------------- 收集数据库统计信息 ----------------------------------------
  procedure GATHER_DATEBASE_STATISTICS is
  begin
    -- 收集表BOARD的统计信息
    dbms_stats.gather_table_stats(ownname          => 'SCOTT',
                                  tabname          => 'BOARD',
                                  estimate_percent => 100,
                                  cascade          => true,
                                  method_opt       => 'for all columns size 1',
                                  no_invalidate    => false);
  
    -- 收集表BOARD_INDEX的统计信息
    dbms_stats.gather_table_stats(ownname          => 'SCOTT',
                                  tabname          => 'BOARD_INDEX',
                                  estimate_percent => 100,
                                  cascade          => true,
                                  method_opt       => 'for all columns size 1',
                                  no_invalidate    => false);
  
    -- 收集表FOREIGN_EXCHANGE的统计信息
    dbms_stats.gather_table_stats(ownname          => 'SCOTT',
                                  tabname          => 'FOREIGN_EXCHANGE',
                                  estimate_percent => 100,
                                  cascade          => true,
                                  method_opt       => 'for all columns size 1',
                                  no_invalidate    => false);
  
    -- 收集表FOREIGN_EXCHANGE_RECORD的统计信息
    dbms_stats.gather_table_stats(ownname          => 'SCOTT',
                                  tabname          => 'FOREIGN_EXCHANGE_RECORD',
                                  estimate_percent => 100,
                                  cascade          => true,
                                  method_opt       => 'for all columns size 1',
                                  no_invalidate    => false);
  
    -- 收集表HIS_ROBOT_ACCOUNT的统计信息
    dbms_stats.gather_table_stats(ownname          => 'SCOTT',
                                  tabname          => 'HIS_ROBOT_ACCOUNT',
                                  estimate_percent => 100,
                                  cascade          => true,
                                  method_opt       => 'for all columns size 1',
                                  no_invalidate    => false);
  
    -- 收集表HIS_ROBOT_ACCOUNT_LOG的统计信息
    dbms_stats.gather_table_stats(ownname          => 'SCOTT',
                                  tabname          => 'HIS_ROBOT_ACCOUNT_LOG',
                                  estimate_percent => 100,
                                  cascade          => true,
                                  method_opt       => 'for all columns size 1',
                                  no_invalidate    => false);
  
    -- 收集表HIS_ROBOT_STOCK_FILTER的统计信息
    dbms_stats.gather_table_stats(ownname          => 'SCOTT',
                                  tabname          => 'HIS_ROBOT_STOCK_FILTER',
                                  estimate_percent => 100,
                                  cascade          => true,
                                  method_opt       => 'for all columns size 1',
                                  no_invalidate    => false);
  
    -- 收集表HIS_ROBOT_TRANSACTION_RECORD的统计信息
    dbms_stats.gather_table_stats(ownname          => 'SCOTT',
                                  tabname          => 'HIS_ROBOT_TRANSACTION_RECORD',
                                  estimate_percent => 100,
                                  cascade          => true,
                                  method_opt       => 'for all columns size 1',
                                  no_invalidate    => false);
  
    -- 收集表MDL_ALL_GOLD_CROSS的统计信息
    dbms_stats.gather_table_stats(ownname          => 'SCOTT',
                                  tabname          => 'MDL_ALL_GOLD_CROSS',
                                  estimate_percent => 100,
                                  cascade          => true,
                                  method_opt       => 'for all columns size 1',
                                  no_invalidate    => false);
  
    -- 收集表MDL_CLOSE_PRICE_MA5_DEAD_CROSS的统计信息
    dbms_stats.gather_table_stats(ownname          => 'SCOTT',
                                  tabname          => 'MDL_CLOSE_PRICE_MA5_DEAD_CROSS',
                                  estimate_percent => 100,
                                  cascade          => true,
                                  method_opt       => 'for all columns size 1',
                                  no_invalidate    => false);
  
    -- 收集表MDL_CLOSE_PRICE_MA5_GOLD_CROSS的统计信息
    dbms_stats.gather_table_stats(ownname          => 'SCOTT',
                                  tabname          => 'MDL_CLOSE_PRICE_MA5_GOLD_CROSS',
                                  estimate_percent => 100,
                                  cascade          => true,
                                  method_opt       => 'for all columns size 1',
                                  no_invalidate    => false);
  
    -- 收集表MDL_G_C_D_C_ANALYSIS的统计信息
    dbms_stats.gather_table_stats(ownname          => 'SCOTT',
                                  tabname          => 'MDL_G_C_D_C_ANALYSIS',
                                  estimate_percent => 100,
                                  cascade          => true,
                                  method_opt       => 'for all columns size 1',
                                  no_invalidate    => false);
  
    -- 收集表MDL_HEI_KIN_ASHI_DOWN_UP的统计信息
    dbms_stats.gather_table_stats(ownname          => 'SCOTT',
                                  tabname          => 'MDL_HEI_KIN_ASHI_DOWN_UP',
                                  estimate_percent => 100,
                                  cascade          => true,
                                  method_opt       => 'for all columns size 1',
                                  no_invalidate    => false);
  
    -- 收集表MDL_HEI_KIN_ASHI_UP_DOWN的统计信息
    dbms_stats.gather_table_stats(ownname          => 'SCOTT',
                                  tabname          => 'MDL_HEI_KIN_ASHI_UP_DOWN',
                                  estimate_percent => 100,
                                  cascade          => true,
                                  method_opt       => 'for all columns size 1',
                                  no_invalidate    => false);
  
    -- 收集表MDL_KD_DEAD_CROSS的统计信息
    dbms_stats.gather_table_stats(ownname          => 'SCOTT',
                                  tabname          => 'MDL_KD_DEAD_CROSS',
                                  estimate_percent => 100,
                                  cascade          => true,
                                  method_opt       => 'for all columns size 1',
                                  no_invalidate    => false);
  
    -- 收集表MDL_KD_GOLD_CROSS的统计信息
    dbms_stats.gather_table_stats(ownname          => 'SCOTT',
                                  tabname          => 'MDL_KD_GOLD_CROSS',
                                  estimate_percent => 100,
                                  cascade          => true,
                                  method_opt       => 'for all columns size 1',
                                  no_invalidate    => false);
  
    -- 收集表MDL_MACD_DEAD_CROSS的统计信息
    dbms_stats.gather_table_stats(ownname          => 'SCOTT',
                                  tabname          => 'MDL_MACD_DEAD_CROSS',
                                  estimate_percent => 100,
                                  cascade          => true,
                                  method_opt       => 'for all columns size 1',
                                  no_invalidate    => false);
  
    -- 收集表MDL_MACD_GOLD_CROSS的统计信息
    dbms_stats.gather_table_stats(ownname          => 'SCOTT',
                                  tabname          => 'MDL_MACD_GOLD_CROSS',
                                  estimate_percent => 100,
                                  cascade          => true,
                                  method_opt       => 'for all columns size 1',
                                  no_invalidate    => false);
  
    -- 收集表MDL_MA_PATTERN_OVERSOLD的统计信息
    dbms_stats.gather_table_stats(ownname          => 'SCOTT',
                                  tabname          => 'MDL_MA_PATTERN_OVERSOLD',
                                  estimate_percent => 100,
                                  cascade          => true,
                                  method_opt       => 'for all columns size 1',
                                  no_invalidate    => false);
  
    -- 收集表MDL_PERCENTAGE_MA_GOLD_CROSS的统计信息
    dbms_stats.gather_table_stats(ownname          => 'SCOTT',
                                  tabname          => 'MDL_PERCENTAGE_MA_GOLD_CROSS',
                                  estimate_percent => 100,
                                  cascade          => true,
                                  method_opt       => 'for all columns size 1',
                                  no_invalidate    => false);
  
    -- 收集表MDL_STOCK_ANALYSIS的统计信息
    dbms_stats.gather_table_stats(ownname          => 'SCOTT',
                                  tabname          => 'MDL_STOCK_ANALYSIS',
                                  estimate_percent => 100,
                                  cascade          => true,
                                  method_opt       => 'for all columns size 1',
                                  no_invalidate    => false);
  
    -- 收集表MDL_STOCK_MONTH_ANALYSIS的统计信息
    dbms_stats.gather_table_stats(ownname          => 'SCOTT',
                                  tabname          => 'MDL_STOCK_MONTH_ANALYSIS',
                                  estimate_percent => 100,
                                  cascade          => true,
                                  method_opt       => 'for all columns size 1',
                                  no_invalidate    => false);
  
    -- 收集表MDL_TOP_STOCK的统计信息
    dbms_stats.gather_table_stats(ownname          => 'SCOTT',
                                  tabname          => 'MDL_TOP_STOCK',
                                  estimate_percent => 100,
                                  cascade          => true,
                                  method_opt       => 'for all columns size 1',
                                  no_invalidate    => false);
  
    -- 收集表MDL_TOP_STOCK_DETAIL的统计信息
    dbms_stats.gather_table_stats(ownname          => 'SCOTT',
                                  tabname          => 'MDL_TOP_STOCK_DETAIL',
                                  estimate_percent => 100,
                                  cascade          => true,
                                  method_opt       => 'for all columns size 1',
                                  no_invalidate    => false);
  
    -- 收集表MDL_ALL_GOLD_CROSS的统计信息
    dbms_stats.gather_table_stats(ownname          => 'SCOTT',
                                  tabname          => 'MDL_WEEK_ALL_GOLD_CROSS',
                                  estimate_percent => 100,
                                  cascade          => true,
                                  method_opt       => 'for all columns size 1',
                                  no_invalidate    => false);
  
    -- 收集表MDL_WEEK_BOLL_C_P_MA5_D_C的统计信息
    dbms_stats.gather_table_stats(ownname          => 'SCOTT',
                                  tabname          => 'MDL_WEEK_BOLL_C_P_MA5_D_C',
                                  estimate_percent => 100,
                                  cascade          => true,
                                  method_opt       => 'for all columns size 1',
                                  no_invalidate    => false);
  
    -- 收集表MDL_WEEK_BOLL_C_P_MA5_G_C的统计信息
    dbms_stats.gather_table_stats(ownname          => 'SCOTT',
                                  tabname          => 'MDL_WEEK_BOLL_C_P_MA5_G_C',
                                  estimate_percent => 100,
                                  cascade          => true,
                                  method_opt       => 'for all columns size 1',
                                  no_invalidate    => false);
  
    -- 收集表MDL_WEEK_BOLL_H_K_A_DOWN_UP的统计信息
    dbms_stats.gather_table_stats(ownname          => 'SCOTT',
                                  tabname          => 'MDL_WEEK_BOLL_H_K_A_DOWN_UP',
                                  estimate_percent => 100,
                                  cascade          => true,
                                  method_opt       => 'for all columns size 1',
                                  no_invalidate    => false);
  
    -- 收集表MDL_WEEK_BOLL_H_K_A_UP_DOWN的统计信息
    dbms_stats.gather_table_stats(ownname          => 'SCOTT',
                                  tabname          => 'MDL_WEEK_BOLL_H_K_A_UP_DOWN',
                                  estimate_percent => 100,
                                  cascade          => true,
                                  method_opt       => 'for all columns size 1',
                                  no_invalidate    => false);
  
    -- 收集表MDL_WEEK_BOLL_KD_DEAD_CROSS的统计信息
    dbms_stats.gather_table_stats(ownname          => 'SCOTT',
                                  tabname          => 'MDL_WEEK_BOLL_KD_DEAD_CROSS',
                                  estimate_percent => 100,
                                  cascade          => true,
                                  method_opt       => 'for all columns size 1',
                                  no_invalidate    => false);
  
    -- 收集表MDL_WEEK_BOLL_KD_GOLD_CROSS的统计信息
    dbms_stats.gather_table_stats(ownname          => 'SCOTT',
                                  tabname          => 'MDL_WEEK_BOLL_KD_GOLD_CROSS',
                                  estimate_percent => 100,
                                  cascade          => true,
                                  method_opt       => 'for all columns size 1',
                                  no_invalidate    => false);
  
    -- 收集表MDL_WEEK_BOLL_MACD_DEAD_CROSS的统计信息
    dbms_stats.gather_table_stats(ownname          => 'SCOTT',
                                  tabname          => 'MDL_WEEK_BOLL_MACD_DEAD_CROSS',
                                  estimate_percent => 100,
                                  cascade          => true,
                                  method_opt       => 'for all columns size 1',
                                  no_invalidate    => false);
  
    -- 收集表MDL_WEEK_BOLL_MACD_GOLD_CROSS的统计信息
    dbms_stats.gather_table_stats(ownname          => 'SCOTT',
                                  tabname          => 'MDL_WEEK_BOLL_MACD_GOLD_CROSS',
                                  estimate_percent => 100,
                                  cascade          => true,
                                  method_opt       => 'for all columns size 1',
                                  no_invalidate    => false);
  
    -- 收集表MDL_WEEK_KD_GOLD_CROSS的统计信息
    dbms_stats.gather_table_stats(ownname          => 'SCOTT',
                                  tabname          => 'MDL_WEEK_KD_GOLD_CROSS',
                                  estimate_percent => 100,
                                  cascade          => true,
                                  method_opt       => 'for all columns size 1',
                                  no_invalidate    => false);
  
    -- 收集表MODEL的统计信息
    dbms_stats.gather_table_stats(ownname          => 'SCOTT',
                                  tabname          => 'MODEL',
                                  estimate_percent => 100,
                                  cascade          => true,
                                  method_opt       => 'for all columns size 1',
                                  no_invalidate    => false);
  
    -- 收集表REAL_ACCOUNT的统计信息
    dbms_stats.gather_table_stats(ownname          => 'SCOTT',
                                  tabname          => 'REAL_ACCOUNT',
                                  estimate_percent => 100,
                                  cascade          => true,
                                  method_opt       => 'for all columns size 1',
                                  no_invalidate    => false);
  
    -- 收集表REAL_STOCK_TRANSACTION_RECORD的统计信息
    dbms_stats.gather_table_stats(ownname          => 'SCOTT',
                                  tabname          => 'REAL_STOCK_TRANSACTION_RECORD',
                                  estimate_percent => 100,
                                  cascade          => true,
                                  method_opt       => 'for all columns size 1',
                                  no_invalidate    => false);
  
    -- 收集表REAL_TRANSACTION_CONDITION的统计信息
    dbms_stats.gather_table_stats(ownname          => 'SCOTT',
                                  tabname          => 'REAL_TRANSACTION_CONDITION',
                                  estimate_percent => 100,
                                  cascade          => true,
                                  method_opt       => 'for all columns size 1',
                                  no_invalidate    => false);
  
    -- 收集表REPORT的统计信息
    dbms_stats.gather_table_stats(ownname          => 'SCOTT',
                                  tabname          => 'REPORT',
                                  estimate_percent => 100,
                                  cascade          => true,
                                  method_opt       => 'for all columns size 1',
                                  no_invalidate    => false);
  
    -- 收集表ROBOT_ACCOUNT的统计信息
    dbms_stats.gather_table_stats(ownname          => 'SCOTT',
                                  tabname          => 'ROBOT_ACCOUNT',
                                  estimate_percent => 100,
                                  cascade          => true,
                                  method_opt       => 'for all columns size 1',
                                  no_invalidate    => false);
  
    -- 收集表ROBOT_ACCOUNT_LOG的统计信息
    dbms_stats.gather_table_stats(ownname          => 'SCOTT',
                                  tabname          => 'ROBOT_ACCOUNT_LOG',
                                  estimate_percent => 100,
                                  cascade          => true,
                                  method_opt       => 'for all columns size 1',
                                  no_invalidate    => false);
  
    -- 收集表ROBOT_STOCK_FILTER统计信息
    dbms_stats.gather_table_stats(ownname          => 'SCOTT',
                                  tabname          => 'ROBOT_STOCK_FILTER',
                                  estimate_percent => 100,
                                  cascade          => true,
                                  method_opt       => 'for all columns size 1',
                                  no_invalidate    => false);
  
    -- 收集表ROBOT_STOCK_TRANSACTION_RECORD统计信息
    dbms_stats.gather_table_stats(ownname          => 'SCOTT',
                                  tabname          => 'ROBOT_STOCK_TRANSACTION_RECORD',
                                  estimate_percent => 100,
                                  cascade          => true,
                                  method_opt       => 'for all columns size 1',
                                  no_invalidate    => false);
  
    -- 收集表ROBOT3_ACCOUNT的统计信息
    dbms_stats.gather_table_stats(ownname          => 'SCOTT',
                                  tabname          => 'ROBOT3_ACCOUNT',
                                  estimate_percent => 100,
                                  cascade          => true,
                                  method_opt       => 'for all columns size 1',
                                  no_invalidate    => false);
  
    -- 收集表ROBOT3_ACCOUNT_LOG的统计信息
    dbms_stats.gather_table_stats(ownname          => 'SCOTT',
                                  tabname          => 'ROBOT3_ACCOUNT_LOG',
                                  estimate_percent => 100,
                                  cascade          => true,
                                  method_opt       => 'for all columns size 1',
                                  no_invalidate    => false);
  
    -- 收集表ROBOT3_STOCK_FILTER统计信息
    dbms_stats.gather_table_stats(ownname          => 'SCOTT',
                                  tabname          => 'ROBOT3_STOCK_FILTER',
                                  estimate_percent => 100,
                                  cascade          => true,
                                  method_opt       => 'for all columns size 1',
                                  no_invalidate    => false);
  
    -- 收集表ROBOT3_STOCK_TRANSACT_RECORD统计信息
    dbms_stats.gather_table_stats(ownname          => 'SCOTT',
                                  tabname          => 'ROBOT3_STOCK_TRANSACT_RECORD',
                                  estimate_percent => 100,
                                  cascade          => true,
                                  method_opt       => 'for all columns size 1',
                                  no_invalidate    => false);
  
    -- 收集表STOCK_INDEX的统计信息
    dbms_stats.gather_table_stats(ownname          => 'SCOTT',
                                  tabname          => 'STOCK_INDEX',
                                  estimate_percent => 100,
                                  cascade          => true,
                                  method_opt       => 'for all columns size 1',
                                  no_invalidate    => false);
  
    -- 收集表STOCK_INDEX_WEEK的统计信息
    dbms_stats.gather_table_stats(ownname          => 'SCOTT',
                                  tabname          => 'STOCK_INDEX_WEEK',
                                  estimate_percent => 100,
                                  cascade          => true,
                                  method_opt       => 'for all columns size 1',
                                  no_invalidate    => false);
  
    -- 收集表STOCK_INFO统计信息
    dbms_stats.gather_table_stats(ownname          => 'SCOTT',
                                  tabname          => 'STOCK_INFO',
                                  estimate_percent => 100,
                                  cascade          => true,
                                  method_opt       => 'for all columns size 1',
                                  no_invalidate    => false);
  
    -- 收集表STOCK_MONTH的统计信息
    dbms_stats.gather_table_stats(ownname          => 'SCOTT',
                                  tabname          => 'STOCK_MONTH',
                                  estimate_percent => 100,
                                  cascade          => true,
                                  method_opt       => 'for all columns size 1',
                                  no_invalidate    => false);
  
    -- 收集表ETF_INFO的统计信息
    dbms_stats.gather_table_stats(ownname          => 'SCOTT',
                                  tabname          => 'ETF_INFO',
                                  estimate_percent => 100,
                                  cascade          => true,
                                  method_opt       => 'for all columns size 1',
                                  no_invalidate    => false);
  
    -- 收集表ETF_TRANSACTION_DATA的统计信息
    dbms_stats.gather_table_stats(ownname          => 'SCOTT',
                                  tabname          => 'ETF_TRANSACTION_DATA',
                                  estimate_percent => 100,
                                  cascade          => true,
                                  method_opt       => 'for all columns size 1',
                                  no_invalidate    => false);
  
    -- 收集表STOCK_TOP_RANKING的统计信息
    dbms_stats.gather_table_stats(ownname          => 'SCOTT',
                                  tabname          => 'STOCK_TOP_RANKING',
                                  estimate_percent => 100,
                                  cascade          => true,
                                  method_opt       => 'for all columns size 1',
                                  no_invalidate    => false);
  
    -- 收集表STOCK_TRANSACTION_DATA的统计信息
    dbms_stats.gather_table_stats(ownname          => 'SCOTT',
                                  tabname          => 'STOCK_TRANSACTION_DATA',
                                  estimate_percent => 100,
                                  cascade          => true,
                                  method_opt       => 'for all columns size 1',
                                  no_invalidate    => false);
  
    -- 收集表STOCK_TRANSACTION_DATA的统计信息
    dbms_stats.gather_table_stats(ownname          => 'SCOTT',
                                  tabname          => 'STOCK_TRANSACTION_DATA_ALL',
                                  estimate_percent => 100,
                                  cascade          => true,
                                  method_opt       => 'for all columns size 1',
                                  no_invalidate    => false);
  
    -- 收集表STOCK_WEEK的统计信息
    dbms_stats.gather_table_stats(ownname          => 'SCOTT',
                                  tabname          => 'STOCK_WEEK',
                                  estimate_percent => 100,
                                  cascade          => true,
                                  method_opt       => 'for all columns size 1',
                                  no_invalidate    => false);
  end GATHER_DATEBASE_STATISTICS;

  ------------------  对STOCK_INFO表的数据进行清洗。去掉code_字段前两个字符，如SH、SZ ------------------
  procedure CLEAN_STOCK_INFO_TABLE is
    -- code_字段的长度
    v_code_length number;
  begin
    -- 查询code_字段的长度
    select distinct length(si.code_) into v_code_length from stock_info si;
    -- 如果code_字段的长度大于6，则去掉code_字段前两个字符
    if v_code_length > 6 then
      dbms_output.put_line('去掉code_字段前两个字符');
      update stock_info si set si.code_ = substr(si.code_, 3);
      commit;
    end if;
  end CLEAN_STOCK_INFO_TABLE;

  ------------------  计算表STOCK_TRANSACTION_DATA的字段last_close_price的值 ------------------
  procedure FILL_LAST_C_P_IN_S_T_D is
    -- 股票代码
    v_code varchar(10);
    -- 表示前收盘
    v_last_close_price number;
    -- 每个股票的code_字段，没有重复的
    cursor cur_distinct_code is
      select distinct t.code_ from stock_transaction_data t;
    -- 某一只股票的全部记录，按照日期升序排列
    cursor cur_single_stock_order_by_asc is
      select *
        from stock_transaction_data t
       where t.code_ = v_code
       order by t.date_ asc;
  begin
    for i in cur_distinct_code loop
      v_code := i.code_;
      for j in cur_single_stock_order_by_asc loop
        -- 计算前收盘，计算公式：前收盘=收盘价/(1+涨跌幅/100)
        v_last_close_price := round(j.close_price /
                                    (1 + j.change_range / 100),
                                    2);
        -- 更新前收盘
        update stock_transaction_data
           set last_close_price = v_last_close_price
         where id_ = j.id_;
      end loop;
      commit;
    end loop;
  end FILL_LAST_C_P_IN_S_T_D;

  -----------------------------------  打印日志到日志文件 -----------------------------------
  procedure DEBUG(message in varchar2) is
    -- 创建UTL_FILE.file_type对象，用于读写文件
    file_handle UTL_FILE.file_type;
  begin
    file_handle := UTL_FILE.FOPEN('LOGDIR', 'oracle-debug.log', 'A');
    UTL_FILE.PUT_LINE(file_handle,
                      to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss') ||
                      ' [debug] ' || message);
    UTL_FILE.FCLOSE(file_handle);
  end DEBUG;

end PKG_TOOL;